#include <time.h>
#include <sqlite3.h>

#include <webhttpd/Basic.h>

#include <srclink/utils.h>
#include <srclink/backend.h>
#include <srclink/static_response.h>

void init_backend() {
    add_route("^/api/get.*", &api_get_route); // 获取数据
    add_route("^/api/del.*", &api_del_route); // 获取数据
    add_route("^/api/search.*", &api_search_route); // 搜索
    add_route("^/api/edit.*", &api_edit_route); // 编辑
    add_route("^/api/add.*", &api_add_route); // 添加
    add_route("^/api/sort.*", &api_sort_route); // 姓名排序
    add_route("^/api/stat.*", &api_stat_route); // 统计信息
}

// 获取学生信息
HttpResponse api_get_route(HttpRequest request) {
    int page = 1, limit = 5;

    // 解析 url 参数
    GHashTable *dict = parse_querys(request.path);

    if (dict) {
        char *value = (char *) g_hash_table_lookup(dict, "page");

        if (value) {
            char *endptr;
            int num = (int) strtol(value, &endptr, 10);
            if (*endptr == '\0' && num > 0) {
                // 检查合法性
                page = num;
            }
        }

        value = (char *) g_hash_table_lookup(dict, "limit");
        if (value) {
            char *endptr;
            int num = (int) strtol(value, &endptr, 10);
            if (*endptr == '\0' && num > 0) {
                // 检查合法性
                limit = num;
            }
        }

        g_hash_table_destroy(dict);
    }

    HttpResponse response;
    response.status_code = 200;
    response.headers = g_hash_table_new_full(g_str_hash, g_str_equal, g_free, g_free);
    g_hash_table_insert(response.headers, g_strdup("Content-Type"), g_strdup("application/json; charset=utf-8"));

    // 打开数据库连接
    sqlite3 *db;
    int rc = sqlite3_open(DATEBASE_NAME, &db);

    if (rc != SQLITE_OK) {
        return error_server_response(request);
    }

    // 获取表中全部数量
    const char *count_query = "SELECT COUNT(*) FROM students;";
    sqlite3_stmt *count_stmt;

    rc = sqlite3_prepare_v2(db, count_query, -1, &count_stmt, NULL);
    if (rc != SQLITE_OK) {
        sqlite3_close(db);
        return error_server_response(request);
    }

    int total_count = 0;
    if (sqlite3_step(count_stmt) == SQLITE_ROW) {
        total_count = sqlite3_column_int(count_stmt, 0);
    }
    sqlite3_finalize(count_stmt);

    // 构建分页查询 SQL
    const char *sql_template = "SELECT * FROM students LIMIT ? OFFSET ?;";
    sqlite3_stmt *stmt;

    rc = sqlite3_prepare_v2(db, sql_template, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        sqlite3_close(db);
        return error_server_response(request);
    }

    // 设置分页参数
    sqlite3_bind_int(stmt, 1, limit); // 第 1 个参数是 LIMIT
    sqlite3_bind_int(stmt, 2, (page - 1) * limit); // 第 2 个参数是 OFFSET

    // 查询数据并转为 GList
    GList *result_list = stmt_to_dict_list(stmt);
    sqlite3_finalize(stmt);
    sqlite3_close(db);

    json_object *data_array = g_list_to_json_array(result_list);

    char *json_string = (char *)json_object_to_json_string_ext(
        data_api_json(0, "success", total_count, data_array),
        JSON_C_TO_STRING_PRETTY);

    // 设置响应体
    response.body.content = malloc(strlen(json_string) + 1);
    response.body.size = strlen(json_string);
    strcpy(response.body.content, json_string);

    // 释放资源
    free(json_string);
    free_dict_list(result_list);
    json_object_put(data_array);

    return response;
}

HttpResponse api_del_route(HttpRequest request) {
    int uid = -1;

    // 解析 url 参数
    GHashTable *dict = parse_querys(request.path);
    if (dict) {
        char *value = (char *) g_hash_table_lookup(dict, "uid");
        if (value) {
            char *endptr;
            int num = (int) strtol(value, &endptr, 10);
            if (*endptr == '\0' && num > 0) {
                // 检查合法性
                uid = num;
            }
        } else {
            // 这里没有提供参数报错
            return normal_response(400, -1, "未提供参数");
        }
        g_hash_table_destroy(dict);
    }

    // 这里正式开始操作数据库

    sqlite3 *db;
    int rc = sqlite3_open(DATEBASE_NAME, &db);

    const char *sql_template = "DELETE FROM students WHERE uid = ?;";
    sqlite3_stmt *stmt;

    rc = sqlite3_prepare_v2(db, sql_template, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        sqlite3_close(db);
        return error_server_response(request);
    }

    // 设置分页参数
    sqlite3_bind_int(stmt, 1, uid);

    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE) {
        fprintf(stderr, "Failed to execute statement: %s\n", sqlite3_errmsg(db));
        sqlite3_finalize(stmt);
        sqlite3_close(db);
        return normal_response(500, -1, "删除失败");
    }

    // 释放资源
    sqlite3_finalize(stmt);
    sqlite3_close(db);

    return normal_response(200, 0, "删除成功");
}

HttpResponse api_search_route(HttpRequest request) {
    int page = 1, limit = 5;

    char *query = malloc(BUFFER_SIZE);
    memset(query, 0, BUFFER_SIZE);

    // 解析 URL 参数
    GHashTable *dict = parse_querys(request.path);
    if (dict) {
        strcpy_s(query, BUFFER_SIZE, g_hash_table_lookup(dict, "query"));

        char *value = (char *) g_hash_table_lookup(dict, "page");
        if (value) {
            char *endptr;
            int num = (int) strtol(value, &endptr, 10);
            if (*endptr == '\0' && num > 0) {
                // 检查合法性
                page = num;
            }
        }

        value = (char *) g_hash_table_lookup(dict, "limit");
        if (value) {
            char *endptr;
            int num = (int) strtol(value, &endptr, 10);
            if (*endptr == '\0' && num > 0) {
                // 检查合法性
                limit = num;
            }
        }

        g_hash_table_destroy(dict);
    }

    if (!query || strlen(query) == 0) {
        free(query); // 释放内存
        return normal_response(400, -1, "缺少参数");
    }

    HttpResponse response;
    response.status_code = 200;
    response.headers = g_hash_table_new_full(g_str_hash, g_str_equal, g_free, g_free);
    g_hash_table_insert(response.headers, g_strdup("Content-Type"), g_strdup("application/json; charset=utf-8"));

    // 打开数据库连接
    sqlite3 *db;
    int rc = sqlite3_open(DATEBASE_NAME, &db);

    if (rc != SQLITE_OK) {
        free(query); // 释放内存
        return normal_response(500, -1, "服务器错误");
    }

    // 查询总条目数量
    const char *count_query = "SELECT COUNT(*) FROM students WHERE \
        uid LIKE ? OR name LIKE ? OR birth LIKE ? OR address LIKE ?;";
    sqlite3_stmt *count_stmt;

    rc = sqlite3_prepare_v2(db, count_query, -1, &count_stmt, NULL);
    if (rc != SQLITE_OK) {
        sqlite3_close(db);
        free(query); // 释放内存
        return normal_response(500, -1, "查询准备失败");
    }

    // 构建模糊查询参数
    char like_query[256];
    snprintf(like_query, sizeof(like_query), "%%%s%%", query);

    sqlite3_bind_text(count_stmt, 1, like_query, -1, SQLITE_STATIC); // uid
    sqlite3_bind_text(count_stmt, 2, like_query, -1, SQLITE_STATIC); // name
    sqlite3_bind_text(count_stmt, 3, like_query, -1, SQLITE_STATIC); // birth
    sqlite3_bind_text(count_stmt, 4, like_query, -1, SQLITE_STATIC); // address

    int total_count = 0;
    if (sqlite3_step(count_stmt) == SQLITE_ROW) {
        total_count = sqlite3_column_int(count_stmt, 0);
    }
    sqlite3_finalize(count_stmt);

    // 构建分页查询 SQL
    const char *sql_template = "SELECT * FROM students WHERE \
        uid LIKE ? OR name LIKE ? OR birth LIKE ? OR address LIKE ? LIMIT ? OFFSET ?;";
    sqlite3_stmt *stmt;

    rc = sqlite3_prepare_v2(db, sql_template, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        sqlite3_close(db);
        free(query); // 释放内存
        return error_server_response(request);
    }

    sqlite3_bind_text(stmt, 1, like_query, -1, SQLITE_STATIC); // uid
    sqlite3_bind_text(stmt, 2, like_query, -1, SQLITE_STATIC); // name
    sqlite3_bind_text(stmt, 3, like_query, -1, SQLITE_STATIC); // birth
    sqlite3_bind_text(stmt, 4, like_query, -1, SQLITE_STATIC); // address
    sqlite3_bind_int(stmt, 5, limit); // LIMIT
    sqlite3_bind_int(stmt, 6, (page - 1) * limit); // OFFSET

    // 查询数据并转为 GList
    GList *result_list = stmt_to_dict_list(stmt);
    sqlite3_finalize(stmt);
    sqlite3_close(db);
    free(query); // 释放内存

    json_object *data_array = g_list_to_json_array(result_list);

    char *json_string = (char *)json_object_to_json_string_ext(
        data_api_json(0, "success", total_count, data_array),
        JSON_C_TO_STRING_PRETTY);

    // 设置响应体
    response.body.content = malloc(strlen(json_string) + 1);
    response.body.size = strlen(json_string);
    strcpy(response.body.content, json_string);

    // 释放资源
    free(json_string);
    free_dict_list(result_list);
    json_object_put(data_array);

    return response;
}


HttpResponse api_edit_route(HttpRequest request) {
    GHashTable *dict = parse_querys(request.path);

    // 学号
    char *uid = malloc(100);
    memset(uid, 0, 100);
    strcpy_s(uid, 100, g_hash_table_lookup(dict, "uid"));

    // 姓名
    char *name = malloc(100);
    memset(name, 0, 100);
    strcpy_s(name, 100, g_hash_table_lookup(dict, "name"));

    // 性别
    char *sex = malloc(10);
    memset(sex, 0, 10);
    strcpy_s(sex, 10, g_hash_table_lookup(dict, "sex"));

    // 出生日期
    char *birth = malloc(100);
    memset(birth, 0, 100);
    strcpy_s(birth, 100, g_hash_table_lookup(dict, "birth"));

    // 家庭住址
    char *address = malloc(BUFFER_SIZE);
    memset(address, 0, BUFFER_SIZE);
    strcpy_s(address, BUFFER_SIZE, g_hash_table_lookup(dict, "address"));

    if (!strlen(uid) || !strlen(name) || !strlen(sex) || !strlen(birth) || !strlen(address)) {
        free(uid);
        free(name);
        free(sex);
        free(birth);
        free(address);
        g_hash_table_destroy(dict);
        return normal_response(400, -1, "参数不足");
    }

    // 验证参数是否有效
    if (strcmp(sex, "男") != 0 && strcmp(sex, "女") != 0) {
        free(uid);
        free(name);
        free(sex);
        free(birth);
        free(address);
        g_hash_table_destroy(dict);
        return normal_response(400, -1, "参数错误");
    }

    // 验证日期
    int year, month, day;

    if (sscanf(birth, "%d-%d-%d", &year, &month, &day) != 3) {
        return normal_response(400, -1, "日期参数错误");
    }

    if (month < 1 || month > 12) {
        return normal_response(400, -1, "日期参数错误");
    }

    if (day < 1 || day > 31) {
        if (month == 6 && day > 29) {
            return normal_response(400, -1, "日期参数错误");
        }
        return normal_response(400, -1, "日期参数错误");
    }

    sprintf(birth, "%4d-%02d-%02d", year, month, day);

    HttpResponse response;
    response.status_code = 200;
    response.headers = g_hash_table_new_full(g_str_hash, g_str_equal, g_free, g_free);
    g_hash_table_insert(response.headers, g_strdup("Content-Type"), g_strdup("application/json; charset=utf-8"));

    // 打开数据库连接
    sqlite3 *db;
    int rc = sqlite3_open(DATEBASE_NAME, &db);

    if (rc != SQLITE_OK) {
        free(uid);
        free(name);
        free(sex);
        free(birth);
        free(address);
        g_hash_table_destroy(dict);
        return normal_response(500, -1, "服务器错误");
    }

    // 构建更新 SQL
    const char *sql_template = "UPDATE students SET name = ?, sex = ?, birth = ?, address = ? WHERE uid = ?;";
    sqlite3_stmt *stmt;

    rc = sqlite3_prepare_v2(db, sql_template, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        sqlite3_close(db);
        free(uid);
        free(name);
        free(sex);
        free(birth);
        free(address);
        g_hash_table_destroy(dict);
        return normal_response(500, -1, "查询准备失败");
    }

    // 绑定参数
    sqlite3_bind_text(stmt, 1, name, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 2, sex, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 3, birth, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 4, address, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 5, uid, -1, SQLITE_STATIC);

    // 执行更新语句
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE) {
        sqlite3_finalize(stmt);
        sqlite3_close(db);
        free(uid);
        free(name);
        free(sex);
        free(birth);
        free(address);
        g_hash_table_destroy(dict);
        return normal_response(500, -1, "更新失败");
    }

    sqlite3_finalize(stmt);
    sqlite3_close(db);

    // 清理资源
    free(uid);
    free(name);
    free(sex);
    free(birth);
    free(address);
    g_hash_table_destroy(dict);

    return normal_response(200, 0, "更新成功");
}

HttpResponse api_add_route(HttpRequest request) {
    GHashTable *dict = parse_querys(request.path);

    // 学号
    char *uid = malloc(100);
    memset(uid, 0, 100);
    strcpy_s(uid, 100, g_hash_table_lookup(dict, "uid"));

    // 姓名
    char *name = malloc(100);
    memset(name, 0, 100);
    strcpy_s(name, 100, g_hash_table_lookup(dict, "name"));

    // 性别
    char *sex = malloc(10);
    memset(sex, 0, 10);
    strcpy_s(sex, 10, g_hash_table_lookup(dict, "sex"));

    // 出生日期
    char *birth = malloc(100);
    memset(birth, 0, 100);
    strcpy_s(birth, 100, g_hash_table_lookup(dict, "birth"));

    // 家庭住址
    char *address = malloc(BUFFER_SIZE);
    memset(address, 0, BUFFER_SIZE);
    strcpy_s(address, BUFFER_SIZE, g_hash_table_lookup(dict, "address"));

    if (!strlen(uid) || !strlen(name) || !strlen(sex) || !strlen(birth) || !strlen(address)) {
        free(uid);
        free(name);
        free(sex);
        free(birth);
        free(address);
        g_hash_table_destroy(dict);
        return normal_response(400, -1, "参数不足");
    }

    // 验证参数是否有效
    if (strcmp(sex, "男") != 0 && strcmp(sex, "女") != 0) {
        free(uid);
        free(name);
        free(sex);
        free(birth);
        free(address);
        g_hash_table_destroy(dict);
        return normal_response(400, -1, "性别参数错误");
    }

    // 验证日期
    int year, month, day;

    if (sscanf(birth, "%d-%d-%d", &year, &month, &day) != 3) {
        free(uid);
        free(name);
        free(sex);
        free(birth);
        free(address);
        g_hash_table_destroy(dict);
        return normal_response(400, -1, "日期参数错误");
    }

    if (month < 1 || month > 12 || day < 1 || day > 31) {
        free(uid);
        free(name);
        free(sex);
        free(birth);
        free(address);
        g_hash_table_destroy(dict);
        return normal_response(400, -1, "日期参数错误");
    }

    sprintf(birth, "%4d-%02d-%02d", year, month, day);

    HttpResponse response;
    response.status_code = 200;
    response.headers = g_hash_table_new_full(g_str_hash, g_str_equal, g_free, g_free);
    g_hash_table_insert(response.headers, g_strdup("Content-Type"), g_strdup("application/json; charset=utf-8"));

    // 打开数据库连接
    sqlite3 *db;
    int rc = sqlite3_open(DATEBASE_NAME, &db);

    if (rc != SQLITE_OK) {
        free(uid);
        free(name);
        free(sex);
        free(birth);
        free(address);
        g_hash_table_destroy(dict);
        return normal_response(500, -1, "服务器错误");
    }

    // 构建插入 SQL
    const char *sql_template = "INSERT INTO students (uid, name, sex, birth, address) VALUES (?, ?, ?, ?, ?);";
    sqlite3_stmt *stmt;

    rc = sqlite3_prepare_v2(db, sql_template, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        sqlite3_close(db);
        free(uid);
        free(name);
        free(sex);
        free(birth);
        free(address);
        g_hash_table_destroy(dict);
        return normal_response(500, -1, "查询准备失败");
    }

    // 绑定参数
    sqlite3_bind_text(stmt, 1, uid, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 2, name, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 3, sex, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 4, birth, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 5, address, -1, SQLITE_STATIC);

    // 执行插入语句
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE) {
        sqlite3_finalize(stmt);
        sqlite3_close(db);
        free(uid);
        free(name);
        free(sex);
        free(birth);
        free(address);
        g_hash_table_destroy(dict);
        return normal_response(500, -1, "插入失败");
    }

    sqlite3_finalize(stmt);
    sqlite3_close(db);

    // 清理资源
    free(uid);
    free(name);
    free(sex);
    free(birth);
    free(address);
    g_hash_table_destroy(dict);

    return normal_response(200, 0, "添加成功");
}


HttpResponse api_sort_route(HttpRequest request) {
    HttpResponse response;
    response.status_code = 200;
    response.headers = g_hash_table_new_full(g_str_hash, g_str_equal, g_free, g_free);
    g_hash_table_insert(response.headers, g_strdup("Content-Type"), g_strdup("text/csv; charset=utf-8"));
    g_hash_table_insert(response.headers, g_strdup("Content-Disposition"), g_strdup("attachment; filename=sorted.csv"));

    // 打开数据库连接
    sqlite3 *db;
    int rc = sqlite3_open(DATEBASE_NAME, &db);

    if (rc != SQLITE_OK) {
        return normal_response(500, -1, "服务器错误");
    }

    // 构建查询 SQL
    const char *sql_query = "SELECT uid, name, sex, birth, address FROM students ORDER BY name ASC;";
    sqlite3_stmt *stmt;

    rc = sqlite3_prepare_v2(db, sql_query, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        sqlite3_close(db);
        return normal_response(500, -1, "查询准备失败");
    }

    // 创建 CSV 内容
    GString *csv_content = g_string_new("uid,name,sex,birth,address\n");

    while (sqlite3_step(stmt) == SQLITE_ROW) {
        const char *uid = (const char *) sqlite3_column_text(stmt, 0);
        const char *name = (const char *) sqlite3_column_text(stmt, 1);
        const char *sex = (const char *) sqlite3_column_text(stmt, 2);
        const char *birth = (const char *) sqlite3_column_text(stmt, 3);
        const char *address = (const char *) sqlite3_column_text(stmt, 4);

        g_string_append_printf(csv_content, "%s,%s,%s,%s,%s\n",
                               uid ? uid : "",
                               name ? name : "",
                               sex ? sex : "",
                               birth ? birth : "",
                               address ? address : "");
    }

    sqlite3_finalize(stmt);
    sqlite3_close(db);

    // 设置响应体，
    const char *content = g_strdup(csv_content->str);
    response.body.size = strlen(content);
    response.body.content = malloc(response.body.size + 1);
    strcpy_s(response.body.content, response.body.size + 1, content);


    return response;
}


HttpResponse api_stat_route(HttpRequest request) {
    // 打开数据库连接
    sqlite3 *db;
    int rc = sqlite3_open(DATEBASE_NAME, &db);

    if (rc != SQLITE_OK) {
        return normal_response(500, -1, "服务器错误");
    }

    // 构建查询 SQL
    const char *sql_query = "SELECT birth FROM students;";
    sqlite3_stmt *stmt;

    rc = sqlite3_prepare_v2(db, sql_query, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        sqlite3_close(db);
        return normal_response(500, -1, "查询准备失败");
    }

    char tags[4][15] = {"17岁", "18岁", "19岁", "19岁以上"};
    int counts[4] = {0, 0, 0, 0};

    // 今年日期
    time_t t = time(NULL);
    struct tm *tm_info = localtime(&t);
    int now_year = tm_info->tm_year + 1900;

    while (sqlite3_step(stmt) == SQLITE_ROW) {
        const char *birth = (const char *) sqlite3_column_text(stmt, 0);
        int year, month, day;
        sscanf(birth, "%4d-%d-%d", &year, &month, &day);
        if (now_year - year == 17) {
            counts[0]++;
        } else if (now_year - year == 18) {
            counts[1]++;
        } else if (now_year - year == 19) {
            counts[2]++;
        } else if (now_year - year > 19) {
            counts[3]++;
        }
    }

    sqlite3_finalize(stmt);
    sqlite3_close(db);

    // 创建 JSON 对象
    struct json_object *json_obj = json_object_new_object();

    // 创建 JSON 数组并填充 tags
    struct json_object *tags_array = json_object_new_array();
    for (int i = 0; i < 4; i++) {
        json_object_array_add(tags_array, json_object_new_string(tags[i]));
    }

    // 创建 JSON 数组并填充 counts
    struct json_object *counts_array = json_object_new_array();
    for (int i = 0; i < 4; i++) {
        json_object_array_add(counts_array, json_object_new_int(counts[i]));
    }

    // 将 tags 和 counts 添加到 JSON 对象中
    json_object_object_add(json_obj, "tags", tags_array);
    json_object_object_add(json_obj, "counts", counts_array);

    char *json_string = (char *)json_object_to_json_string_ext(
        data_api_json(0, "success", 0, json_obj),
        JSON_C_TO_STRING_PRETTY);

    HttpResponse response;
    response.status_code = 200;
    response.headers = g_hash_table_new_full(g_str_hash, g_str_equal, g_free, g_free);
    g_hash_table_insert(response.headers, g_strdup("Content-Type"), g_strdup("application/json; charset=utf-8"));

    // 设置响应体
    response.body.content = malloc(strlen(json_string) + 1);
    response.body.size = strlen(json_string);
    strcpy(response.body.content, json_string);

    free(json_string);

    return response;
}
